package com.xingchen.loveparttimejob.uitls;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 数据库连接关闭工具
 */
public class JDBCUtils {
    private static  String driverClassName;
    private static String url;
    private static   String username;
    private static String password;
    private  Connection connection;//预存的连接
    private static JDBCUtils instance;//当前的实例对象

    static {
        //静态加载properties文件，获取里面的值
        Properties properties = new Properties();
        try {
            InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(resourceAsStream);
            resourceAsStream.close();
            // 通过静态方法来给实例变量赋值，调用时会触发实例创建（如果还未创建）
            driverClassName = properties.getProperty("driverClassName");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 私有构造函数，防止外部直接实例化
     */
    private JDBCUtils() {
        getConnection();
    }

    /**
     * 是否开启事务
     * @return 是否开启
     */
    public boolean isOpenTransaction() {
        boolean isOpenTransaction = true;
        try {
            //默认获取的是true，true就是没开启事务
            isOpenTransaction = connection.getAutoCommit();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //返回是否开启事务，所以取反
        return !isOpenTransaction;
    }

    /**
     * 获取当前的对象
     * @return 获取当前的实例对象
     */
    public static JDBCUtils getInstance() {
        if (instance == null) {
            synchronized (JDBCUtils.class) {
                if (instance == null) {
                    instance = new JDBCUtils();
                }
            }
        }
        return instance;
    }

    /**
     * 获取数据库连接
     */
    public void getConnection(){
        //如果连接不为空，就不重新连接
        if(connection != null){
            return;
        }

        try {
            if (driverClassName != null && !driverClassName.isEmpty()) {
                Class.forName(driverClassName);
            }
            connection = DriverManager.getConnection(url,username,password);

        }catch(ClassNotFoundException | SQLException e){
            throw new RuntimeException(e);
        }

    }

    /**
     * 是否开启事务
     * @param open 是否开启
     */
    public void openTransaction(boolean open){
        try {
            connection.setAutoCommit(!open);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 提交事务
     * @param closeDB 是否关闭数据库连接
     */
    public void commitTransaction(boolean closeDB) {
        try {
            if (connection!= null && !connection.getAutoCommit()) {
                connection.commit();
            }
            if (closeDB) {
                close(null, null, connection);
            }
        } catch (SQLException e) {
            throw new RuntimeException("提交事务失败", e);
        }
    }

    /**
     * 回滚事务
     * @param closeDB 是否关闭数据库连接
     */
    public void rollbackTransaction(boolean closeDB) {
        try {
            if (connection!= null && !connection.getAutoCommit()) {
                connection.rollback();
            }
            if (closeDB) {
                close(null, null, connection);
            }
        } catch (SQLException e) {
            throw new RuntimeException("回滚事务失败", e);
        }
    }

    /**
     * 关闭释放资源
     * @param resultSet 释放结果集资源
     * @param preparedStatement 释放预处理资源
     * @param conn 关闭数据库连接
     */
    private void close(ResultSet resultSet, PreparedStatement preparedStatement,Connection conn) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (conn != null) {
                conn.close();
                connection = null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 执行修改，增加，删除语句
     * @param sql 要执行的sql
     * @param objects 预处理的对象
     * @param closeDB 需不要关闭连接
     * @return 返回执行了多少行
     */
    public Integer executeUpdate(String sql, List<Object> objects,boolean closeDB) throws SQLException {
        //如果里面的连接为空，那么就重新获取一下连接
        if(connection == null){
            getConnection();
        }
        PreparedStatement preparedStatement = null;
        Integer count = 0;
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < objects.size(); i++) {
            preparedStatement.setObject(i+1,objects.get(i));
        }
        count = preparedStatement.executeUpdate();
        //是否要关闭连接
        if(closeDB){
            getInstance().close(null,preparedStatement,connection);
        }else{
            getInstance().close(null,preparedStatement,null);
        }
        return count;
    }

    /**
     * 执行sql语句，返回ArrayList集合
     * @param sql 执行的sql语句
     * @param objects 预处理的数据集合
     * @param fieldMapping 字段映射集合
     * @param clazz 当前类型的字节码
     * @param closeDB 是否关闭数据库
     * @return 返回当前对象的ArrayList集合
     */
    public <T> ArrayList<T> executeQuery(String sql, List<Object> objects, Map<String,String> fieldMapping, Class<T> clazz, boolean closeDB) throws SQLException {
        //如果里面的连接为空，那么就重新获取一下连接
        if(connection == null){
            getConnection();
        }
        ArrayList<T> arrayList = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 声明对象
        arrayList = new ArrayList<T>();
        preparedStatement = connection.prepareStatement(sql);
        // 注入参数
        for(int i = 0; i < objects.size(); i++){
            preparedStatement.setObject(i + 1, objects.get(i));
        }
        // 获取结果集
        resultSet = preparedStatement.executeQuery();
        // 数据存储进数组
        while(resultSet.next()){
            arrayList.add(getInfo(resultSet,fieldMapping,clazz));
        }
        // 释放资源
        if (closeDB){
            getInstance().close(resultSet,preparedStatement,connection);
        }else {
            getInstance().close(resultSet,preparedStatement,null);
        }
        return arrayList;
    }

    /**
     * 返回当前的查询数据的总条数或者自增的id
     * @param sql 执行的sql语句
     * @param objects 预处理的对象
     * @param closeDB 需不要关闭连接
     * @return 返回多少条数据
     */
    public Integer executeQueryCountOrId(String sql, List<Object> objects,boolean closeDB) throws SQLException {
        //连接为空，获取一下连接
        if (connection == null) {
            getConnection();
        }
        int count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //1.注入sql
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < objects.size(); i++) {
            preparedStatement.setObject(i+1,objects.get(i));
        }
        resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            count = resultSet.getInt(1);
        }
        // 释放资源
        if (closeDB){
            getInstance().close(resultSet,preparedStatement,connection);
        }else {
            getInstance().close(resultSet,preparedStatement,null);
        }
        return count;
    }

    /**
     * 获取当前的对象
     * @param resultSet 要获取的结果集
     * @param fieldMapping 要映射的字段啊
     * @param clazz 当前对象的字节码文件
     * @return 返回当前的对象
     */
    private <T> T getInfo(ResultSet resultSet,Map<String, String> fieldMapping,Class<T> clazz){
        //1.新建对象
        T entity = null;
        try {
            entity = clazz.getDeclaredConstructor().newInstance();
            //2.获取所有字段
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                //使用暴力反射操作字段
                field.setAccessible(true);
                //获取集合映射字段
                String columnName = fieldMapping.get(field.getName());
                if(columnName != null){
                    Class<?> fieldType = field.getType();//对象字段类型
                    Object value = resultSet.getObject(columnName);//数据库字段数据
                    if (value != null) {//对比字段类型在进行强制转换
                        if (fieldType == Integer.class || fieldType == int.class) {//Integer类型
                            value = ((Number) value).intValue();
                        } else if (fieldType == Double.class || fieldType == double.class) {//Double类型
                            value = ((Number) value).doubleValue();
                        } else if (fieldType == Long.class || fieldType == long.class) {//Long类型
                            value = ((Number) value).longValue();
                        } else if (fieldType == Float.class || fieldType == float.class) {//Float类型
                            value = ((Number) value).floatValue();
                        } else if (fieldType == byte[].class) {//byte[]数组类型
                            value = resultSet.getBytes(columnName);
                        } else if (fieldType == Date.class) {//Date时间类型
                            value = resultSet.getDate(columnName);
                        } else if (fieldType == Timestamp.class) {//Timestamp时间类型
                            value = resultSet.getTimestamp(columnName);
                        }
                    }
                    //赋值
                    field.set(entity,value);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return entity;
    }

}
